ORSANCO Database Infrastructure Update and Integration Work Plan

Overview & Goals

The goal of this project is to design, create, implement, and populate a database infrastructure to meet ORSANCO’s variety of requirements. The new database infrastructure should be capable of absorbing the data from existing ORSANCO priority databases and it should permit efficient entry of new data. In addition, the new architecture should include robust database servers that support the user queries and report generation in a responsive interface.

The system needs to provide the following:

• Efficient migration of existing data while avoiding the duplication of data,
• Easy-to-use User Interfaces
  • Responsive internal user interfaces to support the Create, Read, Update and Delete (CRUD) operations with multiple levels of authorization,
  • Provide robust mechanisms to generate the pre-defined internal and external reports,
  • Provide an appropriate interface for external users,
  • Create a seamless user experience and user interface, and
  • Create easy-to-use mechanisms for uploading new data.
• Efficient scalable time-series data storage.
• Automated extract, transform and load (ETL) and export functionalities.
• Advanced visualization and analytics.
• Extensibility beyond the initial set of data and datatypes.
• Advanced workflow integration capability

The project will be carried out in multiple phases. The project will begin with a “Discovery Phase” that explores the seven priority databases in detail and creates a system architecture that supports desired functionality. GQC has carried out an initial review of the seven priority databases (six in Access format and one in Excel) and has determined that these databases vary significantly in complexity. The entity-relationship diagrams of the Access databases are provided in the Appendix that summarizes the table layouts and the dependencies. It will be necessary to interact with the users of these databases in a very structured and regular manner to understand the nuances. GQC has substantial experience in implementing such databases and we have developed efficient processes for capturing the technical and functional requirements. Apart from getting into the nitty-gritty of the data, GQC will also simultaneously explore the best architecture for hosting that data. Both tactical and strategic considerations will be considered in the architecture selection process. In addition, the ability to support both on-premises implementation and cloud hosting will be a significant factor.

We have implemented several US EPA databases that provided a consistency of look and feel along with a sense “US EPA” branding. We will explore how that can be done for ORSANCO. The “Discovery Phase” will also define a set of tasks for executing the next phase (Phase 1) and define the effort level involved for each task. The “Phase 1” will primarily address the migration of the seven priority databases into the new infrastructure and implement the user interfaces and the pre-defined reports. Some level of visualization and analytics capabilities will also be implemented in “Phase 1.”

Deliverables

The consultant will be responsible for delivering the following after the “Discovery Phase”:

1. A detailed analysis report for each of the seven priority databases,
2. A proposed ERD for each of those databases,
3. User interface strategy document that outlines navigation, key pages, and visual style,
4. Draft user interfaces for review, iteration, and agreement,
5. Draft system design for review, iteration, and agreement,
6. A detailed project plan for Phase 1 along the list of deliverables.

The Process

The process of gathering the functional requirements (what the software should do) and the technical requirement (how the software should be built) to generate the deliverables will be carried out with the “Agile” project management methodology. For example, “use cases” will be documented with industry standard format along with the correct definitions of the actors involved in each use case. Each database exploration will be carried out in parallel with the appropriate subject matter experts (SME’s) for that database. The outputs from the parallel efforts will be communicated with ORSANCO on a regular basis to solicit their input. There will be a kick-off meeting, one or more interim progress report meetings and a final report delivery meeting.

Timeline (Some stages may run in parallel)

Stage 1 (1 month): Initial meeting, research, & Interviews

The interactions will be carried out virtually or in-person as necessary. There will be a kick-off meeting. A detailed functional requirements document will be created based on the interactions.

Stage 2 (1 month): Weekly Check-In & revisions

Every week, the consultant will ask for feedback, suggestions, and make any adjustments to ensure the requirements are being met.

Stage 3 (1 month): Final Review & project handover

The draft final report will be discussed in a meeting. Any feedback will be included in the final report as appropriate. Consultant Fees